from PyQt4.QtCore import *
from PyQt4.QtGui import *
from PyQt4.QtSql import *
import Ui_billingUtility
import billingUtilEdit
import master.utility as utility

class BillingUtil(QDialog, Ui_billingUtility.Ui_billUtilDlg):
  def __init__(self, dbConn, dataGlob,  parent=None):
        super(BillingUtil, self).__init__(parent)
        self.setupUi(self)       
        self.initMenu()
        self.db = dbConn
        self.dataGlob = dataGlob
        self.util = utility.Utility(self.db)
        self.per_le.setText(self.util.getServerToday().date().toString("MMyy"))
        self.mast_pgb.setVisible(False)
        
        self.mastModel = ModelMaster(self)
        self.mastModel.setEditStrategy(QSqlTableModel.OnManualSubmit)
        self.mastModel.setHeaderData(0, Qt.Horizontal, QVariant("Invoice No"))
        self.mastModel.setHeaderData(1, Qt.Horizontal, QVariant("Unit"))
        self.mastModel.setHeaderData(2, Qt.Horizontal, QVariant("Unit Name"))
        self.mastModel.setHeaderData(3, Qt.Horizontal, QVariant("Electricity"))
        self.mastModel.setHeaderData(4, Qt.Horizontal, QVariant("Water"))
        self.mastModel.setHeaderData(5, Qt.Horizontal, QVariant("Gas"))
        self.mastModel.setHeaderData(6, Qt.Horizontal, QVariant("Stamp Duty"))
        self.mastModel.setHeaderData(7, Qt.Horizontal, QVariant("Total Utility"))
        
        self.master_tbv.setModel(self.mastModel)
        self.master_tbv.setItemDelegate(MasterDelegate(self))
        self.master_tbv.setSelectionMode(QTableView.SingleSelection)
        self.master_tbv.setSelectionBehavior(QTableView.SelectRows)        
        
        
  @pyqtSignature("")
  def on_per_le_editingFinished(self):
      if self.per_le.text() != "":
          self.per_cb.setCheckState(Qt.Checked)
      else:
          self.per_cb.setCheckState(Qt.Unchecked)
        
  @pyqtSignature("")
  def on_unit_le_editingFinished(self):
      if self.unit_le.text() != "":
          self.unit_cb.setCheckState(Qt.Checked)
      else:
          self.unit_cb.setCheckState(Qt.Unchecked)
        
  @pyqtSignature("int")
  def on_bill_cmb_currentIndexChanged(self, idx):
      self.bill_cb.setCheckState(Qt.Checked)
        
  def initMenu(self):
      self.retrieveAct = QAction(QIcon(":/retrieve.ico"), self.tr("Retrieve"), self)
      self.connect(self.retrieveAct, SIGNAL("triggered()"), self.retrieveData)
      self.sortAct = QAction(QIcon(":/sort.ico"), self.tr("Sort"), self)
      
      self.genAct = QAction(QIcon(":/add.ico"), self.tr("Process Billing"), self)
      self.connect(self.genAct, SIGNAL("triggered()"), self.generateData)
      self.postAct = QAction(QIcon(":/add.ico"), self.tr("Posting Billing"), self)
      self.connect(self.postAct, SIGNAL("triggered()"), self.postingData)
      
      self.addAct = QAction(QIcon(":/add.ico"), self.tr("Add"), self)
      self.connect(self.addAct, SIGNAL("triggered()"), self.addData)
      self.editAct = QAction(QIcon(":/edit.ico"), self.tr("Edit"), self)
      self.connect(self.editAct, SIGNAL("triggered()"), self.editData)
      self.delAct = QAction(QIcon(":/del.ico"), self.tr("Delete"), self)
      self.connect(self.delAct, SIGNAL("triggered()"), self.delData)
      
      self.printAct = QAction(QIcon("res/print.ico"), self.tr("Print Billing"), self)
      self.connect(self.printAct, SIGNAL("triggered()"), self.printData)
      self.printRinciAct = QAction(QIcon("res/print.ico"), self.tr("Print Calculation"), self)
      self.connect(self.printRinciAct, SIGNAL("triggered()"), self.printRinci)
      self.printRptAct = QAction(QIcon("res/print.ico"), self.tr("Print Report"), self)
      self.connect(self.printRptAct, SIGNAL("triggered()"), self.printReport)
      
  def retrieveData(self):
      qStr = "select m.no_inv, m.kode_unit, m.nama_unit, format(m.listrik,0), format(m.air,0), format(m.gas,0), format(m.materai,0), format((m.listrik+m.air+m.gas+m.materai),0), m.tertagih_util from trans_billing m "
      
      filt = ""
      urut = " order by m.no_inv"
      if self.per_cb.checkState() == Qt.Checked:
          filt = filt + " m.no_inv like '"+self.per_le.text()+"%' and "
      if self.unit_cb.checkState() == Qt.Checked:
          filt = filt + " m.kode_unit like '"+self.unit_le.text()+"%' and "
      if self.bill_cb.checkState() == Qt.Checked:
          if self.bill_cmb.currentIndex() == 1:
              filt = filt + " m.tertagih_util = 'CBP10001' and "
          elif self.bill_cmb.currentIndex() == 2:
              filt = filt + " m.tertagih_util <> 'CBP10001' and "
      if filt != "":
          filt = " where "+filt.left(len(filt)-4)
          
      qStr = qStr + filt + urut
      print qStr
      query = QSqlQuery(qStr)
      self.mastModel.setQuery(query)
      self.master_tbv.setColumnWidth(0, 70)
      self.master_tbv.setColumnWidth(1, 85)
      self.master_tbv.setColumnWidth(2, 200)
      self.master_tbv.setColumnWidth(3, 150)
      self.master_tbv.setColumnWidth(4, 150)
      self.master_tbv.setColumnWidth(5, 150)
      self.master_tbv.setColumnWidth(6, 150)
      self.master_tbv.setColumnWidth(7, 200)
      self.master_tbv.setColumnHidden(8, True)
      self.master_tbv.resizeRowsToContents()
      
  def generateData(self):
      hari = self.util.getServerToday().date()
      per = hari.toString("MMyy")
      hari_bln_lalu = QDate(hari.year(),  hari.month(),  1)
      hari_bln_lalu = hari_bln_lalu.addDays(-1)
      per_lalu = hari_bln_lalu.toString("MMyy")
      
      query = QSqlQuery()
      query.exec_("select count(*) from trans_billing where left(no_inv,4) = '"+per+"'")
      query.next()
      if query.value(0).toInt()[0] < 1:
          pesan = QMessageBox.question(self, "Billing Utility", "Invoice on '"+per+"' has been created. Abort?", QMessageBox.Yes|QMessageBox.No)
          if pesan == QMessageBox.Yes:
              return
      
      pesan = QMessageBox.question(self, "Billing Utility", "Invoice on '"+per+"' will be created. Continue?", QMessageBox.Yes|QMessageBox.No)
      if pesan == QMessageBox.No:
          return
      
      query.exec_("select count(*) from base_unit_hist where date_format(periode,'%m%y') = '"+per+"'")
      query.next()
      if query.value(0).toInt()[0] < 1:
          QMessageBox.warning(self, "Billing Utility", "Process base unit before generate!")
          return
          
      query.exec_("select awal_sc, akhir_sc, cetak_sc, due_date_sc from master_setup")
      query.next()
      tgl_awal = query.value(0).toDate()
      tgl_akhir = query.value(1).toDate()
      tgl_cetak = query.value(2).toDate()
      tgl_tempo = query.value(3).toDate()
      
      if per != tgl_cetak.toString("MMyy"):
          QMessageBox.warning(self, "Billing Utility", "Please check billing periode! Print date on sistem setup is '"+tgl_cetak.toString("dd-MM-yyyy")+"'")
          return
          
      query.exec_("select count(*) from mater_meteran where left(no_meter,4) = '"+per+"' and validasi = 'N' " )
      query.next()
      if query.value(0).toInt()[0] > 0:
          QMessageBox.warning(self, "Billing Utility", "Validate meter utility first!")
          return
          
      self.master_tbv.setEnabled(False)
      self.meter_pgb.setVisible(True)
# ============== START PROSES ==============          
      utilModel = QSqlTableModel(self)
      utilModel.setTable("trans_billing")
      utilModel.setEditStrategy(QSqlTableModel.OnManualSubmit)
      utilModel.setFilter("left(no_inv,4) = '"+per+"'")
      utilModel.select()
      
      self.detModel = QSqlTableModel(self)
      self.detModel.setTable("trans_billing_detail")
      self.detModel.setEditStrategy(QSqlTableModel.OnManualSubmit)
      self.detModel.setFilter("no_bill like '"+per+"%'")
      self.detModel.select()
      
      self.tarifModel = QSqlQueryModel(self)
      qStr = "select kode_tarif, tarif_satuan, tarif_beban, tarif_retribusi, tarif_administrasi, tarif_daya, rasio, tarif_pemel, max_adm_rp from mater_tarif_detail where status = 'A' order by kode_tarif"
      self.tarifModel.setQuery(qStr, self.db)
      
      self.meterModel = QSqlQueryModel(self)
      qStr = "select no_meter, unit, l_awal, l_akhir, a_awal, a_akhir, g_awal, g_akhir, validasi, bill, left(unit,8) as unit_asli from mater_meteran where left(no_meter,4) = '"+per+"' and bill = 'N' order by unit "
      self.meterModel.setQuery(qStr, self.db)      
      
      self.billModel = QSqlQueryModel(self)
      
      unitModel = QSqlQueryModel(self)
      qStr = "select u.unit, u.nama_unit, u.stat_bill_elc, u.stat_bill_air, u.stat_bill_gas, u.status_join, u.util_bill_to, u.owner, u.tenant, u.buka from base_unit u "
      filt = " where (u.stat_bill_elc = 'Y' or u.stat_bill_air = 'Y' or u.stat_bill_gas = 'Y') and status_join <> 'J' "
      urut = " order by u.unit "
      qStr = qStr + filt + urut
      unitModel.setQuery(qStr, self.db)
      jum_brs = unitModel.rowCount()
      
      kode_dev = "CBP10001"
      urut_m = 0
      self.urut_d = 0
      query.exec_("select convert(max(right(no_inv,4)), signed) from trans_billing where left(no_inv,4) = '"+per+"'")
      while query.next():
          urut_m = query.value(0).toInt()[0]
      query.exec_("select convert(max(right(no_inv,4)), signed) from trans_billing_detail where left(no_inv,4) = '"+per+"'")
      while query.next():
          self.urut_d = query.value(0).toInt()[0]          
          
      self.db.transaction()
      for i in range(jum_brs):
          elc_rp = 0
          wtr_rp = 0
          gas_rp = 0
          total_rp = 0
          met = 0
          denda_rp = 0
          prev_rp = 0
          last_pay = 0
          utilModel.insertRow(i)
          
          urut_m += 1
          no_inv = per + "-" +"0"*(4 - len(str(urut_m)))+str(urut_m)
          utilModel.setData(utilModel.index(i, 0), QVariant(no_inv))
          
          stat_join = unitModel.record(i).value("status_join").toString()
          unit_asli = unitModel.record(i).value("unit").toString()
          kode_unit = unitModel.record(i).value("kode_unit").toString()
          tagih_ke = unitModel.record(i).value("util_bill_to").toString()
          
          if tagih_ke == '3' :
              unit = unit_asli + "-D"
              tagih = kode_dev
          else:
              if stat_join == "N":
                  unit = unit_asli + "-S"
              else:
                  unit = unit_asli + "-G"
                  
              if tagih_ke == "1":
                  tagih = unitModel.record(i).value("owner").toString()
              else:
                  tagih = unitModel.record(i).value("tenant").toString()
              if tagih == kode_dev:
                  unit = unit_asli + "-D"
                  
          utilModel.setData(utilModel.index(i, 1), QVariant(unit))
          utilModel.setData(utilModel.index(i, 14), QVariant(tagih))
          
          #---------------------------------- Electricity ---------------------------------
          if unitModel.record(i).value("stat_bill_elc").toString() == 'Y':
              elc_rp = self.genDetailData(no_inv,  "ELC",  unit_asli)
              utilModel.setData(utilModel.index(i, 7), QVariant(elc_rp))
              utilModel.setData(utilModel.index(i, 24), QVariant(elc_rp))
          
          #---------------------------------- Water ---------------------------------
          if unitModel.record(i).value("stat_bill_air").toString() == 'Y':
              wtr_rp = self.genDetailData(no_inv,  "WTR",  unit_asli)
              utilModel.setData(utilModel.index(i, 8), QVariant(wtr_rp))
              utilModel.setData(utilModel.index(i, 26), QVariant(wtr_rp))
          
          #---------------------------------- Gas ---------------------------------
          if unitModel.record(i).value("stat_bill_gas").toString() == 'Y':
              gas_rp = self.genDetailData(no_inv,  "GAS",  unit_asli)
              utilModel.setData(utilModel.index(i, 9), QVariant(gas_rp))
              utilModel.setData(utilModel.index(i, 28), QVariant(gas_rp))
          
          #---------------------------------- Meterai ---------------------------------
          total_rp = elc_rp + wtr_rp + gas_rp
          if tagih != kode_dev:
              if total_rp > 1000000:
                  met = 6000
              elif total_rp > 250000:
                  met = 3000
          utilModel.setData(utilModel.index(i, 11), QVariant(met))
          utilModel.setData(utilModel.index(i, 30), QVariant(met))
          utilModel.setData(utilModel.index(i, 12), QVariant(total_rp+met))
          
          
          #---------------------------------- Denda ---------------------------------
          if tagih != kode_dev:
              qStr = "select 0.03*(sum(b.listrik+b.air+b.gas+g.materai)) from trans_billing b left outer join trans_payment p on p.no_inv = b.no_inv where left(b.kode_unit,8) = '"+unit_asli+"' and b.tertagih_util <> '"+kode_dev+"' and b.status_pay = 'N' and p.no_payment is null"
              query.exec_(qStr)
              while query.next():
                  denda_rp = query.value(0).toDouble()[0]
          utilModel.setData(utilModel.index(i, 10), QVariant(denda_rp))
          
          #---------------------------------- Prev Balance ---------------------------------
          if tagih != kode_dev:
              qStr = "select listrik+air+gas+materai+prev_balance-last_payment from trans_billing where left(kode_unit,8) = '"+unit_asli+"' and left(no_inv,4) = '"+per_lalu+"'"
              query.exec_(qStr)
              while query.next():
                  prev_rp = query.value(0).toDouble()[0]
          utilModel.setData(utilModel.index(i, 35), QVariant(prev_rp))
          
          #---------------------------------- Last Payment ---------------------------------
          if tagih != kode_dev:
              qStr = "select sum(bayar_listrik)+sum(bayar_air)+sum(bayar_gas)+sum(bayar_materai) from trans_payment where left(kode_unit,8) = '"+unit_asli+"' and status_bill = 'N'"
              query.exec_(qStr)
              while query.next():
                  last_pay = query.value(0).toDouble()[0]
                  if abs(prev_rp - last_pay) < 1000:
                      last_pay = prev_rp
          utilModel.setData(utilModel.index(i, 34), QVariant(last_pay))
          
          #---------------------------------- Other ---------------------------------
          utilModel.setData(utilModel.index(i, 2), QVariant(tgl_cetak))
          utilModel.setData(utilModel.index(i, 3), QVariant(tgl_awal))
          utilModel.setData(utilModel.index(i, 4), QVariant(tgl_akhir))
          utilModel.setData(utilModel.index(i, 5), QVariant(tgl_tempo))
          utilModel.setData(utilModel.index(i, 16), QVariant("N"))
          utilModel.setData(utilModel.index(i, 17), QVariant("N"))
          utilModel.setData(utilModel.index(i, 30), QVariant("N"))
          utilModel.setData(utilModel.index(i, 31), QVariant("N"))
          utilModel.setData(utilModel.index(i, 32), QVariant("N"))
          utilModel.setData(utilModel.index(i, 18), QVariant("validasi"))
          utilModel.setData(utilModel.index(i, 19), QVariant("prepare"))
          utilModel.setData(utilModel.index(i, 20), QVariant("Nama"))
          utilModel.setData(utilModel.index(i, 35), unitModel.record(i).value("nama_unit"))
          
          self.mast_pgb.setValue(i)
          
# ============== END PROSES ==============          
      utilModel.submitAll()
      self.detModel.submitAll()
      self.meterModel.submitAll()
      self.db.commit()
      self.master_tbv.setEnabled(True)
      self.meter_pgb.setVisible(False)
      
  def genDetailData(self,  no_inv,  tipe,  unit):
      per = no_inv.left(4)
      total_rp = 0
      qStr = "select u.unit, b.tipe_tagih, b.kode_tarif, b.no_meter, b.kode_pemilik_penyewa from base_unit_det_bill b left outer join base_unit u on u.kode_unit = b.kode_unit "
      if tipe == 'ELC':
          filt = "b.tipe_tagih in ('ELC', 'LMP') and "
      elif tipe == 'WTR':
          filt = "b.tipe_tagih = 'WTR' and "
      elif tipe == 'GAS':
          filt = "b.tipe_tagih = 'GAS' and "
          
      filt = " where " + filt + " b.billed = 'Y' and u.unit = '"+unit+"'"
      urut = " order by b.tipe_tagih "
      qStr = qStr + filt + urut
      self.billModel.setQuery(qStr, self.db)
      for i in range(self.billModel.rowCount()):
          tarif = self.billModel.record(i).value(2).toString()
          jns_tagih = self.billModel.record(i).value(1).toString()
          no_meter = self.billModel.record(i).value(3).toString()
          
          brs = self.detModel.rowCount()
          self.detModel.insertRow(brs)
          self.urut_d += 1
          no_bill = per + "-" +"0"*(4 - len(str(urut_d)))+str(urut_d)
          self.detModel.setData(self.detModel.index(brs, 0), QVariant(no_bill))
          self.detModel.setData(self.detModel.index(brs, 1), QVariant(no_inv))
          
          self.detModel.setData(self.detModel.index(brs, 2), QVariant(tarif))
          idx_tarif = self.tarifModel.match(self.tarifModel.index(0, 0), 0, tarif,1, Qt.MatchWrap )
          brs_tarif = idx_tarif[0].row()
          satuan = self.tarifModel.record(brs_tarif).value("tarif_satuan").toDouble()[0]
          self.detModel.setData(self.detModel.index(brs, 6), QVariant(satuan))
          beban = self.tarifModel.record(brs_tarif).value("tarif_beban").toDouble()[0]
          self.detModel.setData(self.detModel.index(brs, 7), QVariant(beban))
          daya = self.tarifModel.record(brs_tarif).value("tarif_daya").toDouble()[0]
          self.detModel.setData(self.detModel.index(brs, 9), QVariant(daya))
          ret = self.tarifModel.record(brs_tarif).value("tarif_retribusi").toDouble()[0]
          self.detModel.setData(self.detModel.index(brs, 10), QVariant(ret))
          admin = self.tarifModel.record(brs_tarif).value("tarif_administrasi").toDouble()[0]
          self.detModel.setData(self.detModel.index(brs, 11), QVariant(admin))
          self.detModel.setData(self.detModel.index(brs, 12), self.tarifModel.record(brs_tarif).value("other"))
          self.detModel.setData(self.detModel.index(brs, 14), self.tarifModel.record(brs_tarif).value("prog"))
          self.detModel.setData(self.detModel.index(brs, 15), self.tarifModel.record(brs_tarif).value("tarif2"))
          self.detModel.setData(self.detModel.index(brs, 16), self.tarifModel.record(brs_tarif).value("range1"))
          self.detModel.setData(self.detModel.index(brs, 17), self.tarifModel.record(brs_tarif).value("tarif3"))
          self.detModel.setData(self.detModel.index(brs, 18), self.tarifModel.record(brs_tarif).value("range2"))
          trafo = self.tarifModel.record(brs_tarif).value("trafo").toDouble()[0]
          self.detModel.setData(self.detModel.index(brs, 19), QVariant(trafo))
          self.detModel.setData(self.detModel.index(brs, 20), self.tarifModel.record(brs_tarif).value("tarif_ttlb"))
          self.detModel.setData(self.detModel.index(brs, 21), self.tarifModel.record(brs_tarif).value("tarif_pemel"))
          max_admin = self.tarifModel.record(brs_tarif).value("max_adm_rp").toDouble()[0]
          self.detModel.setData(self.detModel.index(brs, 22), QVariant(max_admin))
          self.detModel.setData(self.detModel.index(brs, 23), QVariant(no_meter))
          rasio = self.tarifModel.record(brs_tarif).value("rasio").toDouble()[0]          
          if jns_tagih == "LMP":
              self.detModel.setData(self.detModel.index(brs, 8), QVariant(rasio*daya))
              rasio = rasio * daya / 1000
          else:
              self.detModel.setData(self.detModel.index(brs, 8), QVariant(rasio))
          
#      qStr = "select no_meter, unit, l_awal, l_akhir, a_awal, a_akhir, g_awal, g_akhir, validasi, bill, left(unit,8) as unit_asli from mater_meteran where left(no_meter,4) = '"+per+"' and bill = 'N' order by unit "
          
          idx_meter = self.meterModel.match(self.meterModel.index(0, 10), 0, unit,1, Qt.MatchWrap )
          brs_meter = idx_meter[0].row()
          if jns_tagih == "ELC":
              awal = self.meterModel.record(brs_meter).value("l_awal").toDouble()[0]
              akhir = self.meterModel.record(brs_meter).value("l_akhir").toDouble()[0]
          elif jns_tagih == "WTR":
              awal = self.meterModel.record(brs_meter).value("a_awal").toDouble()[0]
              akhir = self.meterModel.record(brs_meter).value("a_akhir").toDouble()[0]
          elif jns_tagih == "GAS":
              awal = self.meterModel.record(brs_meter).value("g_awal").toDouble()[0]
              akhir = self.meterModel.record(brs_meter).value("g_akhir").toDouble()[0]
          elif jns_tagih == "LMP":
              awal = 0              
              akhir = trafo*self.util.getServerToday().date().daysInMonth()
          #TODO: set meteran bill ke 'Y'
          qty = akhir - awal
          self.detModel.setData(self.detModel.index(brs, 3), QVariant(awal))
          self.detModel.setData(self.detModel.index(brs, 4), QVariant(akhir))
          self.detModel.setData(self.detModel.index(brs, 5), QVariant(qty))
          
          pakai_rp = (qty*satuan*rasio)+(beban*daya)
          ppju_rp = pakai_rp * ret / 100
          admin_rp = (pakai_rp + ppju_rp)*admin/100
          if admin_rp > max_adm_rp:
              admin_rp = max_adm_rp
          subtotal = pakai_rp + ppju_rp + admin_rp
          total_rp += subtotal
          
      return total_rp
      
      
  def postingData(self):
      query = QSqlQuery()
      query.exec_("select ar_listrik, rv_listrik, ar_air, rv_air, ar_gas, rv_gas, ar_materai, rv_materai, ket_arrv_materai, ket_arrv_denda, ket_arrv_listrik, ket_arrv_air, ket_arrv_gas, jt_listrik, jt_air, jt_gas, jt_materai, from master_setup")
      query.next()
      acc_listrik = query.value(0).toString()
      rv_listrik = query.value(1).toString()
      acc_air = query.value(2).toString()
      rv_air = query.value(3).toString()
      acc_gas = query.value(4).toString()
      rv_gas = query.value(5).toString()
      acc_mat = query.value(6).toString()
      rv_mat = query.value(7).toString()
      ket_mat = query.value(8).toString()
      ket_denda = query.value(9).toString()
      ket_lis = query.value(10).toString()
      ket_air = query.value(11).toString()
      ket_gas = query.value(12).toString()
      jt_lis = query.value(13).toString()
      jt_air = query.value(14).toString()
      jt_gas = query.value(15).toString()
      jt_mat = query.value(16).toString()
      
      periode = self.mastModel.record(1).value("no_inv").toString().left(4)
      query.exec_("select count(*) from trans_billing where posted = 'Y' and periode = '"+periode+"'")
      query.next()
      if query.value(0).toInt()[0] > 0:
          QMessageBox.warning(self,"Billing UTILITY", "This data has been posted. Cannot post again!")
          return
          
#===============      POSTING LISTRIK ===============
      kode = "JV"
      hari = self.util.getServerToday().date()
      jper = hari.toString("MMyyyy")      
      perkode = hari.toString("yyyy-MM")
      username = self.dataGlob["nama"]
      
      #DEV      
      desk = ket_lis + " " + self.util.getServerToday().date().toString("MMMM yyyy") + " DEVELOPER "
      listrik_dev = 0
      query.exec_("select sum(b.listrik) from trans_billing b left outer join master_pemilik_penyewa p on p.kode_pemilik_penyewa = b.tertagih_util where p.kode_tt = 'DEV' and left(b.no_inv,4) = '"+periode+"'")
      while query.next():
          listrik_dev = query.value(0).toDouble()[0]
      if listrik_dev > 0:
          noAkhir = self.util.getNewId(kode, jper, "trans jurnal")
          no_jurnal = kode+"-"+jper+"-"+"0"*(4 - len(str(noAkhir)))+str(noAkhir)
          
          query.prepare("insert into trans_jurnal(tgl_jurnal, no_jurnal, desk_jurnal, kode_user, tgl_entry, kode_periode, jurnal_type) values (:tgl, :no, :desk, :user, :tgl, :per, :tipe)")
          query.bindValue(":tgl", QVariant(hari))
          query.bindValue(":no", QVariant(no_jurnal))
          query.bindValue(":desk", QVariant(desk))
          query.bindValue(":user", QVariant(username))
          query.bindValue(":per", QVariant(perkode))
          query.bindValue(":tipe", QVariant(jt_lis))
          query.exec_()
          
          query.prepare("insert into trans_jurnal_detail(kode_perkiraan, no_jurnal, debit, kredit, urut) values (:kode, :no, :deb, :kre, :urut)")
          query.bindValue(":kode", QVariant(acc_listrik))
          query.bindValue(":no", QVariant(no_jurnal))
          query.bindValue(":deb", QVariant(listrik_dev))
          query.bindValue(":kre", QVariant(0))
          query.bindValue(":urut", QVariant(1))
          query.exec_()
          
          query.bindValue(":kode", QVariant(rv_listrik))
          query.bindValue(":no", QVariant(no_jurnal))
          query.bindValue(":deb", QVariant(0))
          query.bindValue(":kre", QVariant(listrik_dev))
          query.bindValue(":urut", QVariant(2))
          query.exec_()
          
      #BIG TENANT
      
      desk = ket_lis + " " + self.util.getServerToday().date().toString("MMMM yyyy") + " BIG TENANT "
      listrik_bt = 0
      query.exec_("select sum(b.listrik) from trans_billing b left outer join master_pemilik_penyewa p on p.kode_pemilik_penyewa = b.tertagih_util where p.kode_tt = 'BT' and left(b.no_inv,4) = '"+periode+"'")
      while query.next():
          listrik_bt = query.value(0).toDouble()[0]
      if listrik_bt > 0:
          noAkhir = self.util.getNewId(kode, jper, "trans jurnal")
          no_jurnal = kode+"-"+jper+"-"+"0"*(4 - len(str(noAkhir)))+str(noAkhir)
          
          query.prepare("insert into trans_jurnal(tgl_jurnal, no_jurnal, desk_jurnal, kode_user, tgl_entry, kode_periode, jurnal_type) values (:tgl, :no, :desk, :user, :tgl, :per, :tipe)")
          query.bindValue(":tgl", QVariant(hari))
          query.bindValue(":no", QVariant(no_jurnal))
          query.bindValue(":desk", QVariant(desk))
          query.bindValue(":user", QVariant(username))
          query.bindValue(":per", QVariant(perkode))
          query.bindValue(":tipe", QVariant(jt_lis))
          query.exec_()
          
          query.prepare("insert into trans_jurnal_detail(kode_perkiraan, no_jurnal, debit, kredit, urut) values (:kode, :no, :deb, :kre, :urut)")
          query.bindValue(":kode", QVariant(acc_listrik))
          query.bindValue(":no", QVariant(no_jurnal))
          query.bindValue(":deb", QVariant(listrik_bt))
          query.bindValue(":kre", QVariant(0))
          query.bindValue(":urut", QVariant(1))
          query.exec_()
          
          query.bindValue(":kode", QVariant(rv_listrik))
          query.bindValue(":no", QVariant(no_jurnal))
          query.bindValue(":deb", QVariant(0))
          query.bindValue(":kre", QVariant(listrik_bt))
          query.bindValue(":urut", QVariant(2))
          query.exec_()
          
      #TENANT      
      desk = ket_lis + " " + self.util.getServerToday().date().toString("MMMM yyyy") + " TENANT "
      listrik_tu = 0
      query.exec_("select sum(b.listrik) from trans_billing b left outer join master_pemilik_penyewa p on p.kode_pemilik_penyewa = b.tertagih_util where p.kode_tt = 'TU' and left(b.no_inv,4) = '"+periode+"'")
      while query.next():
          listrik_tu = query.value(0).toDouble()[0]
      if listrik_tu > 0:
          noAkhir = self.util.getNewId(kode, jper, "trans jurnal")
          no_jurnal = kode+"-"+jper+"-"+"0"*(4 - len(str(noAkhir)))+str(noAkhir)
          
          query.prepare("insert into trans_jurnal(tgl_jurnal, no_jurnal, desk_jurnal, kode_user, tgl_entry, kode_periode, jurnal_type) values (:tgl, :no, :desk, :user, :tgl, :per, :tipe)")
          query.bindValue(":tgl", QVariant(hari))
          query.bindValue(":no", QVariant(no_jurnal))
          query.bindValue(":desk", QVariant(desk))
          query.bindValue(":user", QVariant(username))
          query.bindValue(":per", QVariant(perkode))
          query.bindValue(":tipe", QVariant(jt_lis))
          query.exec_()
          
          query.prepare("insert into trans_jurnal_detail(kode_perkiraan, no_jurnal, debit, kredit, urut) values (:kode, :no, :deb, :kre, :urut)")
          query.bindValue(":kode", QVariant(acc_listrik))
          query.bindValue(":no", QVariant(no_jurnal))
          query.bindValue(":deb", QVariant(listrik_tu))
          query.bindValue(":kre", QVariant(0))
          query.bindValue(":urut", QVariant(1))
          query.exec_()
          
          query.bindValue(":kode", QVariant(rv_listrik))
          query.bindValue(":no", QVariant(no_jurnal))
          query.bindValue(":deb", QVariant(0))
          query.bindValue(":kre", QVariant(listrik_tu))
          query.bindValue(":urut", QVariant(2))
          query.exec_()
          
#===============    END  POSTING LISTRIK ===============

#===============      POSTING AIR  ===============
      
      #DEV      
      desk = ket_air + " " + self.util.getServerToday().date().toString("MMMM yyyy") + " DEVELOPER "
      air_dev = 0
      query.exec_("select sum(b.air) from trans_billing b left outer join master_pemilik_penyewa p on p.kode_pemilik_penyewa = b.tertagih_util where p.kode_tt = 'DEV' and left(b.no_inv,4) = '"+periode+"'")
      while query.next():
          air_dev = query.value(0).toDouble()[0]
      if air_dev > 0:
          noAkhir = self.util.getNewId(kode, jper, "trans jurnal")
          no_jurnal = kode+"-"+jper+"-"+"0"*(4 - len(str(noAkhir)))+str(noAkhir)
          
          query.prepare("insert into trans_jurnal(tgl_jurnal, no_jurnal, desk_jurnal, kode_user, tgl_entry, kode_periode, jurnal_type) values (:tgl, :no, :desk, :user, :tgl, :per, :tipe)")
          query.bindValue(":tgl", QVariant(hari))
          query.bindValue(":no", QVariant(no_jurnal))
          query.bindValue(":desk", QVariant(desk))
          query.bindValue(":user", QVariant(username))
          query.bindValue(":per", QVariant(perkode))
          query.bindValue(":tipe", QVariant(jt_lis))
          query.exec_()
          
          query.prepare("insert into trans_jurnal_detail(kode_perkiraan, no_jurnal, debit, kredit, urut) values (:kode, :no, :deb, :kre, :urut)")
          query.bindValue(":kode", QVariant(acc_air))
          query.bindValue(":no", QVariant(no_jurnal))
          query.bindValue(":deb", QVariant(air_dev))
          query.bindValue(":kre", QVariant(0))
          query.bindValue(":urut", QVariant(1))
          query.exec_()
          
          query.bindValue(":kode", QVariant(rv_air))
          query.bindValue(":no", QVariant(no_jurnal))
          query.bindValue(":deb", QVariant(0))
          query.bindValue(":kre", QVariant(air_dev))
          query.bindValue(":urut", QVariant(2))
          query.exec_()
          
      #BIG TENANT
      
      desk = ket_air + " " + self.util.getServerToday().date().toString("MMMM yyyy") + " BIG TENANT "
      air_bt = 0
      query.exec_("select sum(b.air) from trans_billing b left outer join master_pemilik_penyewa p on p.kode_pemilik_penyewa = b.tertagih_util where p.kode_tt = 'BT' and left(b.no_inv,4) = '"+periode+"'")
      while query.next():
          air_bt = query.value(0).toDouble()[0]
      if air_bt > 0:
          noAkhir = self.util.getNewId(kode, jper, "trans jurnal")
          no_jurnal = kode+"-"+jper+"-"+"0"*(4 - len(str(noAkhir)))+str(noAkhir)
          
          query.prepare("insert into trans_jurnal(tgl_jurnal, no_jurnal, desk_jurnal, kode_user, tgl_entry, kode_periode, jurnal_type) values (:tgl, :no, :desk, :user, :tgl, :per, :tipe)")
          query.bindValue(":tgl", QVariant(hari))
          query.bindValue(":no", QVariant(no_jurnal))
          query.bindValue(":desk", QVariant(desk))
          query.bindValue(":user", QVariant(username))
          query.bindValue(":per", QVariant(perkode))
          query.bindValue(":tipe", QVariant(jt_lis))
          query.exec_()
          
          query.prepare("insert into trans_jurnal_detail(kode_perkiraan, no_jurnal, debit, kredit, urut) values (:kode, :no, :deb, :kre, :urut)")
          query.bindValue(":kode", QVariant(acc_air))
          query.bindValue(":no", QVariant(no_jurnal))
          query.bindValue(":deb", QVariant(air_bt))
          query.bindValue(":kre", QVariant(0))
          query.bindValue(":urut", QVariant(1))
          query.exec_()
          
          query.bindValue(":kode", QVariant(rv_air))
          query.bindValue(":no", QVariant(no_jurnal))
          query.bindValue(":deb", QVariant(0))
          query.bindValue(":kre", QVariant(air_bt))
          query.bindValue(":urut", QVariant(2))
          query.exec_()
          
      #TENANT      
      desk = ket_air + " " + self.util.getServerToday().date().toString("MMMM yyyy") + " TENANT "
      air_tu = 0
      query.exec_("select sum(b.air) from trans_billing b left outer join master_pemilik_penyewa p on p.kode_pemilik_penyewa = b.tertagih_util where p.kode_tt = 'TU' and left(b.no_inv,4) = '"+periode+"'")
      while query.next():
          air_tu = query.value(0).toDouble()[0]
      if air_tu > 0:
          noAkhir = self.util.getNewId(kode, jper, "trans jurnal")
          no_jurnal = kode+"-"+jper+"-"+"0"*(4 - len(str(noAkhir)))+str(noAkhir)
          
          query.prepare("insert into trans_jurnal(tgl_jurnal, no_jurnal, desk_jurnal, kode_user, tgl_entry, kode_periode, jurnal_type) values (:tgl, :no, :desk, :user, :tgl, :per, :tipe)")
          query.bindValue(":tgl", QVariant(hari))
          query.bindValue(":no", QVariant(no_jurnal))
          query.bindValue(":desk", QVariant(desk))
          query.bindValue(":user", QVariant(username))
          query.bindValue(":per", QVariant(perkode))
          query.bindValue(":tipe", QVariant(jt_lis))
          query.exec_()
          
          query.prepare("insert into trans_jurnal_detail(kode_perkiraan, no_jurnal, debit, kredit, urut) values (:kode, :no, :deb, :kre, :urut)")
          query.bindValue(":kode", QVariant(acc_air))
          query.bindValue(":no", QVariant(no_jurnal))
          query.bindValue(":deb", QVariant(air_tu))
          query.bindValue(":kre", QVariant(0))
          query.bindValue(":urut", QVariant(1))
          query.exec_()
          
          query.bindValue(":kode", QVariant(rv_air))
          query.bindValue(":no", QVariant(no_jurnal))
          query.bindValue(":deb", QVariant(0))
          query.bindValue(":kre", QVariant(air_tu))
          query.bindValue(":urut", QVariant(2))
          query.exec_()
          
#===============    END  POSTING AIR ===============
      
#===============      POSTING GAS  ===============
      
      #DEV      
      desk = ket_gas + " " + self.util.getServerToday().date().toString("MMMM yyyy") + " DEVELOPER "
      gas_dev = 0
      query.exec_("select sum(b.gas) from trans_billing b left outer join master_pemilik_penyewa p on p.kode_pemilik_penyewa = b.tertagih_util where p.kode_tt = 'DEV' and left(b.no_inv,4) = '"+periode+"'")
      while query.next():
          gas_dev = query.value(0).toDouble()[0]
      if gas_dev > 0:
          noAkhir = self.util.getNewId(kode, jper, "trans jurnal")
          no_jurnal = kode+"-"+jper+"-"+"0"*(4 - len(str(noAkhir)))+str(noAkhir)
          
          query.prepare("insert into trans_jurnal(tgl_jurnal, no_jurnal, desk_jurnal, kode_user, tgl_entry, kode_periode, jurnal_type) values (:tgl, :no, :desk, :user, :tgl, :per, :tipe)")
          query.bindValue(":tgl", QVariant(hari))
          query.bindValue(":no", QVariant(no_jurnal))
          query.bindValue(":desk", QVariant(desk))
          query.bindValue(":user", QVariant(username))
          query.bindValue(":per", QVariant(perkode))
          query.bindValue(":tipe", QVariant(jt_lis))
          query.exec_()
          
          query.prepare("insert into trans_jurnal_detail(kode_perkiraan, no_jurnal, debit, kredit, urut) values (:kode, :no, :deb, :kre, :urut)")
          query.bindValue(":kode", QVariant(acc_gas))
          query.bindValue(":no", QVariant(no_jurnal))
          query.bindValue(":deb", QVariant(gas_dev))
          query.bindValue(":kre", QVariant(0))
          query.bindValue(":urut", QVariant(1))
          query.exec_()
          
          query.bindValue(":kode", QVariant(rv_gas))
          query.bindValue(":no", QVariant(no_jurnal))
          query.bindValue(":deb", QVariant(0))
          query.bindValue(":kre", QVariant(gas_dev))
          query.bindValue(":urut", QVariant(2))
          query.exec_()
          
      #BIG TENANT
      
      desk = ket_gas + " " + self.util.getServerToday().date().toString("MMMM yyyy") + " BIG TENANT "
      gas_bt = 0
      query.exec_("select sum(b.air) from trans_billing b left outer join master_pemilik_penyewa p on p.kode_pemilik_penyewa = b.tertagih_util where p.kode_tt = 'BT' and left(b.no_inv,4) = '"+periode+"'")
      while query.next():
          gas_bt = query.value(0).toDouble()[0]
      if gas_bt > 0:
          noAkhir = self.util.getNewId(kode, jper, "trans jurnal")
          no_jurnal = kode+"-"+jper+"-"+"0"*(4 - len(str(noAkhir)))+str(noAkhir)
          
          query.prepare("insert into trans_jurnal(tgl_jurnal, no_jurnal, desk_jurnal, kode_user, tgl_entry, kode_periode, jurnal_type) values (:tgl, :no, :desk, :user, :tgl, :per, :tipe)")
          query.bindValue(":tgl", QVariant(hari))
          query.bindValue(":no", QVariant(no_jurnal))
          query.bindValue(":desk", QVariant(desk))
          query.bindValue(":user", QVariant(username))
          query.bindValue(":per", QVariant(perkode))
          query.bindValue(":tipe", QVariant(jt_lis))
          query.exec_()
          
          query.prepare("insert into trans_jurnal_detail(kode_perkiraan, no_jurnal, debit, kredit, urut) values (:kode, :no, :deb, :kre, :urut)")
          query.bindValue(":kode", QVariant(acc_gas))
          query.bindValue(":no", QVariant(no_jurnal))
          query.bindValue(":deb", QVariant(gas_bt))
          query.bindValue(":kre", QVariant(0))
          query.bindValue(":urut", QVariant(1))
          query.exec_()
          
          query.bindValue(":kode", QVariant(rv_gas))
          query.bindValue(":no", QVariant(no_jurnal))
          query.bindValue(":deb", QVariant(0))
          query.bindValue(":kre", QVariant(gas_bt))
          query.bindValue(":urut", QVariant(2))
          query.exec_()
          
      #TENANT      
      desk = ket_gas + " " + self.util.getServerToday().date().toString("MMMM yyyy") + " TENANT "
      gas_tu = 0
      query.exec_("select sum(b.air) from trans_billing b left outer join master_pemilik_penyewa p on p.kode_pemilik_penyewa = b.tertagih_util where p.kode_tt = 'TU' and left(b.no_inv,4) = '"+periode+"'")
      while query.next():
          gas_tu = query.value(0).toDouble()[0]
      if gas_tu > 0:
          noAkhir = self.util.getNewId(kode, jper, "trans jurnal")
          no_jurnal = kode+"-"+jper+"-"+"0"*(4 - len(str(noAkhir)))+str(noAkhir)
          
          query.prepare("insert into trans_jurnal(tgl_jurnal, no_jurnal, desk_jurnal, kode_user, tgl_entry, kode_periode, jurnal_type) values (:tgl, :no, :desk, :user, :tgl, :per, :tipe)")
          query.bindValue(":tgl", QVariant(hari))
          query.bindValue(":no", QVariant(no_jurnal))
          query.bindValue(":desk", QVariant(desk))
          query.bindValue(":user", QVariant(username))
          query.bindValue(":per", QVariant(perkode))
          query.bindValue(":tipe", QVariant(jt_lis))
          query.exec_()
          
          query.prepare("insert into trans_jurnal_detail(kode_perkiraan, no_jurnal, debit, kredit, urut) values (:kode, :no, :deb, :kre, :urut)")
          query.bindValue(":kode", QVariant(acc_gas))
          query.bindValue(":no", QVariant(no_jurnal))
          query.bindValue(":deb", QVariant(gas_tu))
          query.bindValue(":kre", QVariant(0))
          query.bindValue(":urut", QVariant(1))
          query.exec_()
          
          query.bindValue(":kode", QVariant(rv_gas))
          query.bindValue(":no", QVariant(no_jurnal))
          query.bindValue(":deb", QVariant(0))
          query.bindValue(":kre", QVariant(gas_tu))
          query.bindValue(":urut", QVariant(2))
          query.exec_()
          
#===============    END  POSTING GAS ===============
      
      
#===============      POSTING MATERAI ===============      
      desk = ket_mat + " Utilities Period " + self.util.getServerToday().date().toString("MMMM yyyy") 
      mat = 0
      query.exec_("select sum(materai) from trans_billing where left(no_inv,4) = '"+periode+"'")
      while query.next():
          mat = query.value(0).toDouble()[0]
      if mat > 0:
          noAkhir = self.util.getNewId(kode, jper, "trans jurnal")
          no_jurnal = kode+"-"+jper+"-"+"0"*(4 - len(str(noAkhir)))+str(noAkhir)
          
          query.prepare("insert into trans_jurnal(tgl_jurnal, no_jurnal, desk_jurnal, kode_user, tgl_entry, kode_periode, jurnal_type) values (:tgl, :no, :desk, :user, :tgl, :per, :tipe)")
          query.bindValue(":tgl", QVariant(hari))
          query.bindValue(":no", QVariant(no_jurnal))
          query.bindValue(":desk", QVariant(desk))
          query.bindValue(":user", QVariant(username))
          query.bindValue(":per", QVariant(perkode))
          query.bindValue(":tipe", QVariant(jt_lis))
          query.exec_()
          
          query.prepare("insert into trans_jurnal_detail(kode_perkiraan, no_jurnal, debit, kredit, urut) values (:kode, :no, :deb, :kre, :urut)")
          query.bindValue(":kode", QVariant(acc_mat))
          query.bindValue(":no", QVariant(no_jurnal))
          query.bindValue(":deb", QVariant(mat))
          query.bindValue(":kre", QVariant(0))
          query.bindValue(":urut", QVariant(1))
          query.exec_()
          
          query.bindValue(":kode", QVariant(rv_mat))
          query.bindValue(":no", QVariant(no_jurnal))
          query.bindValue(":deb", QVariant(0))
          query.bindValue(":kre", QVariant(mat))
          query.bindValue(":urut", QVariant(2))
          query.exec_()
          
      query.exec_("update trans_billing set posted = 'Y' where left(no_inv,4) = '"+periode+"'")
      QMessageBox.information(self,  "Billing Utility", "Utility has been posted.")
      
      
  def addData(self):
      form = billingUtilEdit.BillingUtilEdit(self.db, "add",  self.dataGlob, self)
      form.show()

  def editData(self):
      index = self.master_tbv.currentIndex()
      if index.isValid():
          brs = self.mastModel.record(index.row())
          id = brs.value("no_inv").toString()
          form = billingUtilEdit.BillingUtilEdit(self.db, id, self.dataGlob, self)
          form.show()

  def delData(self):
      index = self.master_tbv.currentIndex()
#      if index.isValid():
#          brs = self.mastModel.record(index.row())
#          id = brs.value("kode_bu").toString()
#          nama = brs.value("nama_bu").toString()
#          if QMessageBox.question(self, "Business Unit","Delete Data : "+nama+"?", QMessageBox.Yes|QMessageBox.No) == QMessageBox.Yes:
#              qMast = QSqlQuery(self.db)
#              self.db.transaction()
#              qMast.exec_("delete from master_bisnis_unit where kode_bu = '"+id+"'")
#              self.db.commit()
#              self.retrieveData()

  def sortData(self):
       tes = ""

  def printData(self):
       tes = ""

  def printRinci(self):
       tes = ""

  def printReport(self):
       tes = ""

  def contextMenuEvent(self, event):
        self.menu = QMenu(self)
        self.menu.addAction(self.retrieveAct)
        self.menu.addAction(self.sortAct)
        self.menu.addSeparator()
        self.menu.addAction(self.genAct)
        self.menu.addAction(self.postAct)
        self.menu.addSeparator()
        self.menu.addAction(self.addAct)
        self.menu.addAction(self.editAct)
        self.menu.addSeparator()
        self.menu.addAction(self.delAct)
        self.menu.addSeparator()
        self.menu.addAction(self.printAct)
        self.menu.addAction(self.printRinciAct)
        self.menu.addAction(self.printRptAct)
        self.menu.exec_(event.globalPos())

class ModelMaster(QSqlTableModel):
    def __init__(self, parent=None):
        super(ModelMaster, self).__init__(parent)
        
    def flags(self, index):
        flags = Qt.ItemIsSelectable|Qt.ItemIsEnabled
#        flags = QSqlTableModel.flags(self, index)        
#        if index.column() < 5:
#            flags = Qt.ItemIsSelectable|Qt.ItemIsEnabled
        return flags
        
class MasterDelegate(QItemDelegate):
    def __init__(self, parent=None):
        super(MasterDelegate, self).__init__(parent)
        
    def paint(self, painter, option, index):
        myoption = QStyleOptionViewItem(option)
        if index.column() > 2:
            myoption.displayAlignment |= Qt.AlignRight|Qt.AlignVCenter
        QItemDelegate.paint(self, painter, myoption, index)        
        
        
if __name__ == "__main__":
    import sys
    app = QApplication(sys.argv)
    form = BillingUtil()
    form.show()
    app.exec_()
